package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

/**
 * 留言的SQL语句封装类
 */

@Component
public class MessageSqlProvider {

    //查询所有的未删除的留言
    public String count(){ return "SELECT count(*) FROM t_message WHERE DELETE_STATUS=0"; }

    //通过用户ID查询留言
    public String countByUserId(){ return "SELECT count(*) FROM t_message WHERE USER_ID = #{userId} AND DELETE_STATUS=0"; }

    //查询所有的未删除的留言并且通过添加时间来排序
    public String findListByUserId(){
        return "SELECT * FROM t_message WHERE USER_ID=#{userId} AND DELETE_STATUS=0 ORDER BY ADD_TIME DESC";
    }

    //通过ID查询留言
    public String findById() {
        return "SELECT * FROM t_message WHERE ID = #{id} AND DELETE_STATUS=0";
    }

    //未删除的留言通过发布时间排序
    public String findList(){
        return "SELECT * FROM t_message WHERE DELETE_STATUS=0 ORDER BY ADD_TIME DESC";
    }

    //通过标题模糊查询
    public String findListByQuery(){
        return "SELECT * FROM t_message WHERE DELETE_STATUS=0 AND TITLE LIKE #{query,jdbcType=VARCHAR} ORDER BY ADD_TIME DESC";
    }

    //修改
    public String update(){
        return "UPDATE t_message SET USER_ID=#{userId}," +
                "USER_NAME=#{userName},NAME=#{name},TITLE=#{title},CONTENT=#{content},STATE=#{state} WHERE ID=#{id}";
    }

    //保存
    public String save(){
        return "INSERT INTO t_message(ADD_TIME,USER_ID,USER_NAME,NAME,TITLE,CONTENT,STATE) " +
                "VALUES(NOW(),#{userId},#{userName},#{name},#{title},#{content},#{state})";
    }

    //删除
    public String deleteById(){
        return "UPDATE t_message SET DELETE_STATUS=1 WHERE ID=#{id}";
    }

    public String state0count(){ return "SELECT count(*) FROM t_message WHERE DELETE_STATUS=0 AND STATE=0"; }

    public String state1count(){ return "SELECT count(*) FROM t_message WHERE DELETE_STATUS=0 AND STATE=1"; }

    public String state2count(){ return "SELECT count(*) FROM t_message WHERE DELETE_STATUS=0 AND STATE=2"; }

}
